package jwj8508.j2ee.basic.projectv5.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import jwj8508.j2ee.basic.projectv5.model.BoardVO;
import jwj8508.j2ee.basic.util.jdbc.MariaDB;

public class BoardDAO {
	private static String newSQL = "insert into boardv4(title, uid, contents) values(?, ?, ?)";
	private static String listSQL = "select bdno, title, uid, wdate, reades from boardv4 order by bdno desc";
	private static String viewSQL = "select * from boardv4 where bdno=?";
	private static String readSQL = "update boardv4 set reades=reades+1 where bdno=?";
	private static String updateSQL = "update boardv4 set title=?, uid=?, contents=? where bdno=?";
	private static String deleteSQL = "delete from boardv4 where bdno=?";
	
	public static boolean newBoard(BoardVO b) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try {
			conn = MariaDB.openConn();
			pstmt = conn.prepareStatement(newSQL);
			pstmt.setString(1, b.getTitle());
			pstmt.setString(2, b.getUid());
			pstmt.setString(3, b.getContents());
			if (pstmt.executeUpdate()>0) result = true;			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, null);
		}
		return result;
	}
	
	public static List<BoardVO> listBoard() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<BoardVO> result = new ArrayList<>();
		
		try {
			conn = MariaDB.openConn();
			pstmt = conn.prepareStatement(listSQL);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				BoardVO b = new BoardVO();
				b.setBdno(rs.getString("bdno"));
				b.setTitle(rs.getString("title"));
				b.setUid(rs.getString("uid"));
				b.setWdate(rs.getString("wdate"));
				b.setReades(rs.getString("reades"));
				result.add(b);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, rs);
		}		
		return result;
	}
	
	public static BoardVO viewBoard(BoardVO b) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BoardVO result = null;
		
		try {
			conn = MariaDB.openConn();
			pstmt = conn.prepareStatement(readSQL);
			pstmt.setString(1, b.getBdno());
			pstmt.executeUpdate();
			if (pstmt!=null) try{pstmt.close();}catch(Exception e){}
			
			pstmt = conn.prepareStatement(viewSQL);
			pstmt.setString(1, b.getBdno());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = new BoardVO();
				result.setBdno(b.getBdno());
				result.setTitle(rs.getString("title"));
				result.setUid(rs.getString("uid"));
				result.setContents(rs.getString("contents"));
				result.setWdate(rs.getString("wdate"));
				result.setReades(rs.getString("reades"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, rs);
		}		
		return result;
	}
	
	public static boolean updateBoard(BoardVO b) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = MariaDB.openConn();
			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setString(1, b.getTitle());
			pstmt.setString(2, b.getUid());
			pstmt.setString(3, b.getContents());
			pstmt.setString(4, b.getBdno());
			if (pstmt.executeUpdate()>0) result = true;
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, null);
		}
		return result;
	}

	public static boolean deleteBoard(BoardVO b) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = MariaDB.openConn();
			pstmt = conn.prepareStatement(deleteSQL);
			pstmt.setString(1, b.getBdno());
			if (pstmt.executeUpdate()>0) result = true;
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, null);
		}
		return result;
	}
}
